package com.nmgydy.admin.controller;

import com.alibaba.fastjson.JSON;
import com.nmgydy.beans.ListBean;
import com.nmgydy.beans.Message;
import com.nmgydy.beans.ResultBean;
import com.nmgydy.model.Admin;
import com.nmgydy.model.Court;
import com.nmgydy.model.EventItem;
import com.nmgydy.model.Role;
import com.nmgydy.util.Functions;
import com.nmgydy.util.HibernateDao;
import com.nmgydy.util.QrcodeUtil;
import org.apache.commons.io.FilenameUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.xssf.usermodel.*;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.multipart.commons.CommonsMultipartFile;
import sun.font.FontDesignMetrics;

import javax.annotation.Resource;
import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.awt.*;
import java.awt.image.BufferedImage;
import java.io.*;
import java.math.BigInteger;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Controller
@RequestMapping("/admin/statistics")
public class StatisticsController {

	@Resource
	private HibernateDao hibernateDao;

	@RequestMapping(value = "list", method = RequestMethod.GET)
	public String list(HttpServletRequest request, HttpServletResponse response, Model model) {
		return "/admin/statistics/list";
	}

	@RequestMapping(value = "detail", method = RequestMethod.GET)
	public String detail(Integer overdueDays,HttpServletRequest request, HttpServletResponse response, Model model) {
		String userId = request.getParameter("userId");
		String timeStart = request.getParameter("timeStart");
		String timeEnd = request.getParameter("timeEnd");
		model.addAttribute("userId",userId);
		model.addAttribute("timeStart",timeStart);
		model.addAttribute("timeEnd",timeEnd);
		if(overdueDays == null){
			overdueDays = 0;
		}
		model.addAttribute("overdueDays",overdueDays);
		return "/admin/statistics/detail";
	}

	@RequestMapping(value = "search", method = RequestMethod.GET)
	public String search(HttpServletRequest request, HttpServletResponse response, Model model) {
		return "/admin/statistics/search";
	}

	@RequestMapping(value = "listData", method = RequestMethod.POST)
	@ResponseBody
	public String listData(HttpServletRequest request, HttpServletResponse response, Model model) {
		String createTimeStart = request.getParameter("createTimeStart");
		String createTimeEnd = request.getParameter("createTimeEnd");
		Map<String, Object> params = new HashMap<String, Object>();

		Session session = null;
		try {
			//开始数据库操作
			session = hibernateDao.getSessionFactory().openSession();
			String sql = "select count(*) as total," +
					"sum(case when status='1' then 1 end) as total1," +
					"sum(case when status='2' then 1 end) as total2," +
					"sum(case when status='3' then 1 end) as total3," +
					"sum(case when (status='3' and curDays-totDays=1) then 1 end) as totalToday," +
					"userId,userName from cn_court" +
					" where updateTime >= :createTimeStart and updateTime <= :createTimeEnd group by userId";

			List<?> list = session.createSQLQuery(sql)
					.setParameter("createTimeStart", createTimeStart)
					.setParameter("createTimeEnd", createTimeEnd)
					.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
					.list();

			return JSON.toJSONString(new ListBean(list));
		} catch (Exception e) {
			e.printStackTrace();
			return JSON.toJSONString(ResultBean.failure("查询失败"));
		} finally {
			if (session != null) {
				session.close();
			}
		}
	}

	@RequestMapping(value = "moveSearch", method = RequestMethod.GET)
	public String moveSearch(HttpServletRequest request, HttpServletResponse response, Model model) {
		return "/admin/statistics/moveSearch";
	}

	//流转日报
	@RequestMapping(value = "moveDaily", method = RequestMethod.GET)
	public String moveDaily(HttpServletRequest request, HttpServletResponse response, Model model) {
		String dailyDate = request.getParameter("dailyDate");
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		if(Functions.isEmpty(dailyDate)){
			dailyDate = sdf.format(new Date());
		}
		model.addAttribute("dailyDate",dailyDate);
		return "/admin/statistics/moveDaily";
	}

	@RequestMapping(value = "moveDailyData", method = RequestMethod.POST)
	@ResponseBody
	public String moveDailyData(HttpServletRequest request, HttpServletResponse response, Model model) {
		Admin admin = (Admin) request.getSession().getAttribute("admin");
		String dailyDate = request.getParameter("dailyDate");
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy.MM.dd");
		Session session = null;
		try {
			Date date = sdf.parse(dailyDate);
			//开始数据库操作
			session = hibernateDao.getSessionFactory().openSession();

			HashSet<String> userIdSet = new HashSet<>();
			if(admin.getId() != 1){
				String[] departmentIds = admin.getDepartmentIds().split(",");
				String hql = "from Admin";
				List<Admin> adminList = session.createQuery(hql).list();
				for (int i=0;i<adminList.size();i++){
					Admin adminDb = adminList.get(i);
					if(Functions.isEmpty(adminDb.getDepartmentIds())){
						continue;
					}
					String[] departmentDbIds = adminDb.getDepartmentIds().split(",");
					for(String departmentDbId : departmentDbIds){
						for(String departmentId : departmentIds){
							if(departmentDbId.equals(departmentId)){
								userIdSet.add(String.valueOf(adminDb.getId()));
							}
						}
					}
				}
			}
			String[] userIdArr = userIdSet.toArray(new String[0]);
			String userIds = String.join(",",userIdArr);
			System.out.println("userIds:"+userIds);

			//节点
			String sql = "select courtId,concat(curDays+1,'天') as days,lastTaskName as nodelBegin," +
					" taskName as nodelEnd,(case when lastTotDays<lastCurDays then '是' else '否' end) as isOver" +
					" from cn_move_history where left(moveTime,10)=:dailyDate and lastHistoryId != 0";
			if(admin.getId() != 1){
				sql += " and userId in ('"+userIds+"')";
			}
			List<?> courtTaskList = session.createSQLQuery(sql)
					.setParameter("dailyDate",dailyDate)
					.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
					.list();

			Map<Integer,List<Map<String,Object>>> courtHistoryMap = new HashMap<>();
			for(int i=0;i<courtTaskList.size();i++){
				Map<String,Object> moveHistory = (Map<String, Object>) courtTaskList.get(i);
				Integer courtId = (Integer) moveHistory.get("courtId");
				List<Map<String,Object>> courtHistoryList = courtHistoryMap.get(courtId);
				if(courtHistoryList == null){
					courtHistoryList = new ArrayList<>();
					courtHistoryMap.put(courtId,courtHistoryList);
				}
				courtHistoryList.add(moveHistory);
			}

			//案件
			sql = "select userId,courtId,courtNumber as caseNo,courtName as caseName" +
					" from cn_move_history where left(moveTime,10)=:dailyDate and lastHistoryId != 0";
			if(admin.getId() != 1){
				sql += " and userId in ('"+userIds+"')";
			}
			sql += " group by userId,courtId";
			List<?> courtList = session.createSQLQuery(sql)
					.setParameter("dailyDate",dailyDate)
					.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
					.list();

			Map<Integer,List<Map<String,Object>>> userDetailMap = new HashMap<>();
			for(int i=0;i<courtList.size();i++){
				Map<String,Object> userCourt = (Map<String, Object>) courtList.get(i);
				Integer courtId = (Integer) userCourt.get("courtId");
				List<Map<String,Object>> courtHistoryList = courtHistoryMap.get(courtId);
				if(courtHistoryList == null){
					courtHistoryList = new ArrayList<>();
				}
				userCourt.put("nodelList",courtHistoryList);

				Integer userId = (Integer) userCourt.get("userId");
				List<Map<String,Object>> detailList = userDetailMap.get(userId);
				if(detailList == null){
					detailList = new ArrayList<>();
					userDetailMap.put(userId,detailList);
				}
				detailList.add(userCourt);
			}


			//用户
			sql = "select userId,userName as name,count(*) as total from cn_move_history" +
					" where left(moveTime,10)=:dailyDate and lastHistoryId != 0";
			if(admin.getId() != 1){
				sql += " and userId in ('"+userIds+"')";
			}
			sql += " group by userId";
			List<?> userMoveList = session.createSQLQuery(sql)
					.setParameter("dailyDate",dailyDate)
					.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
					.list();
			for(int i=0;i<userMoveList.size();i++){
				Map<String,Object> userCourt = (Map<String, Object>) userMoveList.get(i);
				Integer userId = (Integer) userCourt.get("userId");
				List<Map<String,Object>> detailList = userDetailMap.get(userId);
				if(detailList == null){
					detailList = new ArrayList<>();
				}
				userCourt.put("detail",detailList);
				BigInteger total = (BigInteger) userCourt.get("total");
				userCourt.put("sumNode",total.intValue()+"个");
				userCourt.put("containCase",detailList.size()+"件");
			}

			Map<String,Object> dataMap = new HashMap<>();
			dataMap.put("title","精细化节点流转情况日报<span onclick='search()'>（"+sdf2.format(date)+"）</span>");
			dataMap.put("date",dailyDate);
			dataMap.put("content",userMoveList);

			return JSON.toJSONString(ResultBean.success(dataMap));
		} catch (Exception e) {
			e.printStackTrace();
			return JSON.toJSONString(ResultBean.failure("查询失败"));
		} finally {
			if (session != null) {
				session.close();
			}
		}
	}

	@RequestMapping(value = "dailySearch", method = RequestMethod.GET)
	public String dailySearch(HttpServletRequest request, HttpServletResponse response, Model model) {
		return "/admin/statistics/dailySearch";
	}

	@RequestMapping(value = "dailyList", method = RequestMethod.GET)
	public String dailyList(HttpServletRequest request, HttpServletResponse response, Model model) {
		String dailyDate = request.getParameter("dailyDate");
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		if(Functions.isEmpty(dailyDate)){
			dailyDate = sdf.format(new Date());
		}
		model.addAttribute("dailyDate",dailyDate);
		return "/admin/statistics/dailyList";
	}

	@RequestMapping(value = "dailyListData", method = RequestMethod.POST)
	@ResponseBody
	public String dailyListData(HttpServletRequest request, HttpServletResponse response, Model model) {
		Admin admin = (Admin) request.getSession().getAttribute("admin");
		String dailyDate = request.getParameter("dailyDate");
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy.MM.dd");
		Session session = null;
		try {
			Date date = sdf.parse(dailyDate);
			//开始数据库操作
			session = hibernateDao.getSessionFactory().openSession();

			HashSet<String> userIdSet = new HashSet<>();
			if(admin.getId() != 1){
				String[] departmentIds = admin.getDepartmentIds().split(",");
				String hql = "from Admin";
				List<Admin> adminList = session.createQuery(hql).list();
				for (int i=0;i<adminList.size();i++){
					Admin adminDb = adminList.get(i);
					if(Functions.isEmpty(adminDb.getDepartmentIds())){
						continue;
					}
					String[] departmentDbIds = adminDb.getDepartmentIds().split(",");
					for(String departmentDbId : departmentDbIds){
						for(String departmentId : departmentIds){
							if(departmentDbId.equals(departmentId)){
								userIdSet.add(String.valueOf(adminDb.getId()));
							}
						}
					}
				}
			}
			String[] userIdArr = userIdSet.toArray(new String[0]);
			String userIds = String.join(",",userIdArr);
			System.out.println("userIds:"+userIds);

			String sql = "select userId,taskId as nodeId,taskName as nodeName," +
					"count(*) as nodeCase,max(curDays) as nodeDay" +
					" from cn_court where status='3'";
			if(admin.getId() != 1){
				sql += " and userId in ('"+userIds+"')";
			}
			sql += " group by userId,taskId";
			List<?> courtTaskList = session.createSQLQuery(sql)
					.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
					.list();
			Map<Integer,List<Map<String,Object>>> courtTaskMap = new HashMap<>();
			for(int i=0;i<courtTaskList.size();i++){
				Map<String,Object> courtTask = (Map<String, Object>) courtTaskList.get(i);
				Integer userId = (Integer) courtTask.get("userId");
				List<Map<String,Object>> userCourtTaskList = courtTaskMap.get(userId);
				if(userCourtTaskList == null){
					userCourtTaskList = new ArrayList<>();
					courtTaskMap.put(userId,userCourtTaskList);
				}
				userCourtTaskList.add(courtTask);
			}

			sql = "select userId as id,userName as name,count(*) as sumCase," +
					"sum(case when status='3' then 1 end) as overCase," +
					"sum(case when status!='3' then 1 end) as normalCase" +
					" from cn_court";
			if(admin.getId() != 1){
				sql += " where userId in ('"+userIds+"')";
			}
			sql += " group by userId";
			List<?> courtList = session.createSQLQuery(sql)
					.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
					.list();
			for(int i=0;i<courtList.size();i++){
				Map<String,Object> courtMap = (Map<String, Object>) courtList.get(i);
				Integer userId = (Integer) courtMap.get("id");
				if(courtTaskMap.get(userId) != null){
					courtMap.put("overNode",courtTaskMap.get(userId));
				}else{
					courtMap.put("overNode",new ArrayList<>());
				}
				if(courtMap.get("overCase") == null){
					courtMap.put("overCase",0);
				}
				if(courtMap.get("normalCase") == null){
					courtMap.put("normalCase ",0);
				}
			}

			Map<String,Object> dataMap = new HashMap<>();
			dataMap.put("title","精细化节点管理汇总（"+sdf2.format(date)+"）");
			dataMap.put("date",dailyDate);
			dataMap.put("detail",courtList);

			return JSON.toJSONString(ResultBean.success(dataMap));
		} catch (Exception e) {
			e.printStackTrace();
			return JSON.toJSONString(ResultBean.failure("查询失败"));
		} finally {
			if (session != null) {
				session.close();
			}
		}
	}

	@RequestMapping(value = "dailyDetail", method = RequestMethod.GET)
	public String dailyDetail(HttpServletRequest request, HttpServletResponse response, Model model) {
		String userId = request.getParameter("userId");
		String taskId = request.getParameter("taskId");
		String type = request.getParameter("type");
		model.addAttribute("userId",userId);
		model.addAttribute("taskId",taskId);
		model.addAttribute("type",type);
		return "/admin/statistics/dailyDetail";
	}

	@RequestMapping(value = "dailyDetailData", method = RequestMethod.POST)
	@ResponseBody
	public String dailyDetailData(HttpServletRequest request, HttpServletResponse response, Model model) {
		String userId = request.getParameter("userId");
		String taskId = request.getParameter("taskId");
		String type = request.getParameter("type");
		String hql = "from Court where userId=:userId";
		Map<String,Object> params = new HashMap<>();
		params.put("userId",Integer.parseInt(userId));
		if(!Functions.isEmpty(taskId)){
			hql += " and taskId=:taskId";
			params.put("taskId",Integer.parseInt(taskId));
		}
		if("normalCase".equals(type)){
			hql += " and status!='3'";
		}
		if("overCase".equals(type)){
			hql += " and status='3'";
		}
		List<?> list = hibernateDao.list(hql,params);
		return JSON.toJSONString(new ListBean(list));
	}

	@RequestMapping(value = "exportDailyDetail", method = RequestMethod.POST)
	@ResponseBody
	public String exportDailyDetail(HttpServletRequest request, HttpServletResponse response, Model model) {
		String userId = request.getParameter("userId");
		String taskId = request.getParameter("taskId");
		String type = request.getParameter("type");
		String hql = "from Court where userId=:userId";
		Map<String,Object> params = new HashMap<>();
		params.put("userId",Integer.parseInt(userId));
		if(!Functions.isEmpty(taskId)){
			hql += " and taskId=:taskId";
			params.put("taskId",Integer.parseInt(taskId));
		}
		if("normalCase".equals(type)){
			hql += " and status!='3'";
		}
		if("overCase".equals(type)){
			hql += " and status='3'";
		}
		List<?> list = hibernateDao.list(hql,params);
		String webPath = request.getSession().getServletContext().getRealPath("/");
		String fileName = buildCourtExcel(webPath,list);
		if(fileName == null){
			return JSON.toJSONString(ResultBean.failure("文件保存失败"));
		}
		//返回下载地址
		String rootPath = request.getContextPath();
		String uploadUrl = rootPath + "/upload/" + fileName;
		return JSON.toJSONString(ResultBean.success(uploadUrl));
	}

	@RequestMapping(value = "detailData", method = RequestMethod.POST)
	@ResponseBody
	public String detailData(Integer overdueDays,HttpServletRequest request, HttpServletResponse response, Model model) {
		String userId = request.getParameter("userId");
		String timeStart = request.getParameter("timeStart");
		String timeEnd = request.getParameter("timeEnd");

		Session session = null;
		try {
			//开始数据库操作
			session = hibernateDao.getSessionFactory().openSession();
			String sql = "from Court where updateTime >= :timeStart and updateTime <= :timeEnd" +
					" and userId=:userId and status='3'";

			if(overdueDays != null && overdueDays > 0){
				sql += " and curDays-totDays=1";
			}

			List<?> list = session.createQuery(sql)
					.setParameter("timeStart", timeStart)
					.setParameter("timeEnd", timeEnd)
					.setParameter("userId", Integer.parseInt(userId))
					.list();

			return JSON.toJSONString(new ListBean(list));
		} catch (Exception e) {
			e.printStackTrace();
			return JSON.toJSONString(ResultBean.failure("查询失败"));
		} finally {
			if (session != null) {
				session.close();
			}
		}
	}

	@RequestMapping(value = "exportDetail", method = RequestMethod.POST)
	@ResponseBody
	public String exportDetail(Integer overdueDays,HttpServletRequest request, HttpServletResponse response, Model model) {
		String userId = request.getParameter("userId");
		String timeStart = request.getParameter("timeStart");
		String timeEnd = request.getParameter("timeEnd");
		Session session = null;
		try {
			//加载数据
			session = hibernateDao.getSessionFactory().openSession();
			String sql = "from Court where updateTime >= :timeStart and updateTime <= :timeEnd" +
					" and userId=:userId and status='3'";
			if(overdueDays != null && overdueDays > 0){
				sql += " and curDays-totDays=1";
			}

			List<?> list = session.createQuery(sql)
					.setParameter("timeStart", timeStart)
					.setParameter("timeEnd", timeEnd)
					.setParameter("userId", Integer.parseInt(userId))
					.list();

			if (list.size() <= 0) {
				return JSON.toJSONString(ResultBean.failure("没有数据"));
			}

			String webPath = request.getSession().getServletContext().getRealPath("/");
			String fileName = buildCourtExcel(webPath,list);
			if(fileName == null){
				return JSON.toJSONString(ResultBean.failure("文件保存失败"));
			}
			//返回下载地址
			String rootPath = request.getContextPath();
			String uploadUrl = rootPath + "/upload/" + fileName;
			return JSON.toJSONString(ResultBean.success(uploadUrl));

		} catch (Exception e) {
			e.printStackTrace();
			return JSON.toJSONString(ResultBean.failure("查询失败"));
		} finally {
			if (session != null) {
				session.close();
			}
		}
	}

	private String buildCourtExcel(String webPath,List<?> list){
		//读取文件
		String tplFileName = "DetailExport.xlsx";
		String path = Court.class.getResource("/").getPath();
		String filePath = path + "../tpl/" + tplFileName;
		File file = new File(filePath);
		XSSFWorkbook workbook = null;
		try {
			FileInputStream fileInputStream = new FileInputStream(file);
			workbook = new XSSFWorkbook(fileInputStream);
		} catch (Exception e) {
			e.printStackTrace();
		}
		XSSFSheet sheet = workbook.getSheet("sheet1");
		//写入文件
		XSSFCellStyle textCellStyle = workbook.createCellStyle();
		textCellStyle.setBorderTop(BorderStyle.THIN);
		textCellStyle.setBorderBottom(BorderStyle.THIN);
		textCellStyle.setBorderLeft(BorderStyle.THIN);
		textCellStyle.setBorderRight(BorderStyle.THIN);

		XSSFDataFormat numberFormart = workbook.createDataFormat();
		XSSFCellStyle numberCellStyle = workbook.createCellStyle();
		numberCellStyle.setAlignment(HorizontalAlignment.RIGHT);
		numberCellStyle.setDataFormat(numberFormart.getFormat("#,##0.00"));
		numberCellStyle.setBorderTop(BorderStyle.THIN);
		numberCellStyle.setBorderBottom(BorderStyle.THIN);
		numberCellStyle.setBorderLeft(BorderStyle.THIN);
		numberCellStyle.setBorderRight(BorderStyle.THIN);

		XSSFCell cell;
		for (int i = 0; i < list.size(); i++) {
			Court court = (Court) list.get(i);
			XSSFRow row = sheet.createRow(i + 1);
			cell = row.createCell(0);
			cell.setCellStyle(textCellStyle);
			cell.setCellValue(court.getUserName());
			cell = row.createCell(1);
			cell.setCellStyle(textCellStyle);
			cell.setCellValue(court.getTaskName());
			cell = row.createCell(2);
			cell.setCellStyle(textCellStyle);
			cell.setCellValue(court.getNumber());
			cell = row.createCell(3);
			cell.setCellStyle(textCellStyle);
			String caseType = court.getCaseType();
			if(caseType.equals("1")){
				cell.setCellValue("民事案件");
			}else if(caseType.equals("2")){
				cell.setCellValue("刑事案件");
			}else if(caseType.equals("3")){
				cell.setCellValue("执行案件");
			}
			cell = row.createCell(4);
			cell.setCellStyle(textCellStyle);
			cell.setCellValue(court.getName());
			cell = row.createCell(5);
			cell.setCellStyle(textCellStyle);
			cell.setCellValue(court.getTotDays());
			cell = row.createCell(6);
			cell.setCellStyle(textCellStyle);
			cell.setCellValue(court.getCurDays());
			cell = row.createCell(7);
			cell.setCellStyle(textCellStyle);
			cell.setCellValue(court.getUpdateTime());
			cell = row.createCell(8);
			String status = court.getStatus();
			cell.setCellStyle(textCellStyle);
			if(status.equals("1")){
				cell.setCellValue("进行中");
			}else if(status.equals("2")){
				cell.setCellValue("已完成");
			}else if(status.equals("3")){
				cell.setCellValue("已逾期");
			}
		}
		//保存文件

		String realFilePath = webPath + "upload";
		if (!(new File(realFilePath).exists())) {
			new File(realFilePath).mkdirs();
		}
		String fileName = Functions.getUUID() + ".xlsx";
		String saveFilePath = realFilePath + File.separator + fileName;
		ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
		try {
			workbook.write(byteArrayOutputStream);
			File outfile = new File(saveFilePath);
			FileOutputStream fileOutputStream = new FileOutputStream(outfile);
			fileOutputStream.write(byteArrayOutputStream.toByteArray());
			fileOutputStream.flush();
			fileOutputStream.close();
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return fileName;

	}

	@RequestMapping(value = "eventList", method = RequestMethod.GET)
	public String eventList(HttpServletRequest request, HttpServletResponse response, Model model) {
		return "/admin/statistics/eventList";
	}

	@RequestMapping(value = "eventListData", method = RequestMethod.POST)
	@ResponseBody
	public String eventListData(HttpServletRequest request, HttpServletResponse response, Model model) {
		String createTimeStart = request.getParameter("createTimeStart");
		String createTimeEnd = request.getParameter("createTimeEnd");
		Map<String, Object> params = new HashMap<String, Object>();

		Session session = null;
		try {
			//开始数据库操作
			session = hibernateDao.getSessionFactory().openSession();
			String sql = "select taskId,taskName,count(*) as total,sum(overdueDays) as overdueDays" +
					",sum(overdueDays)/count(*) as avgDays from cn_event_item" +
					" where eventType='2' and createTime >= :createTimeStart and createTime <= :createTimeEnd group by taskId";

			List<?> list = session.createSQLQuery(sql)
					.setParameter("createTimeStart", createTimeStart)
					.setParameter("createTimeEnd", createTimeEnd)
					.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
					.list();

			return JSON.toJSONString(new ListBean(list));
		} catch (Exception e) {
			e.printStackTrace();
			return JSON.toJSONString(ResultBean.failure("查询失败"));
		} finally {
			if (session != null) {
				session.close();
			}
		}
	}
}
